/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean.session;

import entity.PriceListEntity;
import entity.ProductEntity;
import entity.SalesQuotationEntity;
import entity.SystemUserEntity;
import java.util.ArrayList;
import java.util.Date;
import javax.annotation.Resource;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.ejb.Timeout;
import javax.ejb.Timer;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author wei
 */
@Stateless
public class SalesQuotationEntityFacade extends AbstractFacade<SalesQuotationEntity> {
    @PersistenceContext(unitName = "MacTeam-warPU")
    private EntityManager em;
    @Resource 
    SessionContext ctx;

    protected EntityManager getEntityManager() {
        return em;
    }

    public SalesQuotationEntityFacade() {
        super(SalesQuotationEntity.class);
    }
    
    public DataModel searchSalesQuotationById(String attribute, String input) {
        Query query = em.createQuery("SELECT s FROM SalesQuotation s WHERE s." + attribute + " = " + input + " AND s.archived != 'true'");
        return new ListDataModel(query.getResultList());
    }
    
    public DataModel searchSalesQuotationByName(String attribute, String input) {
        Query querySalesLead = em.createQuery("SELECT s FROM SalesQuotation s INNER JOIN s.salesLead sl WHERE CONCAT(sl.first" + attribute + ", ' ', sl.last" + attribute + 
                ") LIKE '%" + input + "%' AND s.archived != 'true'");
        Query queryCustomer = em.createQuery("SELECT s FROM SalesQuotation s INNER JOIN s.customer c WHERE CONCAT(c.first" + attribute + ", ' ', c.last" + attribute + 
                ") LIKE '%" + input + "%' AND s.archived != 'true'");
        
        ArrayList query = new ArrayList<Object>();
        
        for (Object o : querySalesLead.getResultList()) {
            query.add(o);
        }
        for (Object o : queryCustomer.getResultList()) {
            query.add(o);
        }
        return new ListDataModel(query);
    }
    
    public ProductEntity getSalesQuotationProduct(String productName) {
        Query query = em.createQuery("Select p FROM Product p");
        for(Object o : query.getResultList()) {
            ProductEntity product = (ProductEntity) o;
            if(product.getProductName().equals(productName))
                return product;
        }
        return null;
    }
    
    public DataModel getUnquoted() {
        Query query = em.createQuery("Select DISTINCT s FROM SalesQuotation s INNER JOIN s.pqp p WHERE s.archived != 'true' AND s.valid != 'true'");
        return new ListDataModel(query.getResultList());
    }
    
    public void scheduleTimer(Date validity, long id) { 
      ctx.getTimerService().createTimer(validity, id);  
   }  
  
   @Timeout  
   public void timeoutHandler(Timer timer) {  
      SalesQuotationEntity s = em.find(SalesQuotationEntity.class, timer.getInfo());
      s.setValid(false);
      em.merge(s);  
  
      timer.cancel();  
   }
   
   public SystemUserEntity getManagerFromDept(String department) {
       Query query = em.createQuery("SELECT DISTINCT d.manager FROM Department d WHERE d.name = '" + department + "'");
       SystemUserEntity manager = (SystemUserEntity) query.getSingleResult();
       System.out.println(manager.getName());
       return manager;
   }
   
   public DataModel getAllSalesQuotationForSalesLead(long id) {
        Query query = em.createQuery("SELECT s FROM SalesQuotation s INNER JOIN s.salesLead sl WHERE sl.id = " + id + " AND s.archived != 'true'");
        ArrayList salesQuotation = new ArrayList<SalesQuotationEntity>();
        
        for (Object o : query.getResultList()) {
            SalesQuotationEntity s = (SalesQuotationEntity) o;
            salesQuotation.add(s);
        }
        return new ListDataModel(salesQuotation);
    }
    
    public DataModel getAllSalesQuotationForCustomer(long id) {
        Query query = em.createQuery("SELECT s FROM SalesQuotation s INNER JOIN s.customer c WHERE c.id = " + id + " AND s.archived != 'true'");
        ArrayList salesQuotation = new ArrayList<SalesQuotationEntity>();
        
        for (Object o : query.getResultList()) {
            SalesQuotationEntity s = (SalesQuotationEntity) o;
            salesQuotation.add(s);
        }
        return new ListDataModel(salesQuotation);
    }
    
    public DataModel getAllSalesQuotation() {
        Query query = em.createQuery("SELECT s FROM SalesQuotation s WHERE s.archived!= 'true'");
        return new ListDataModel(query.getResultList());
    }
    
    public ArrayList<PriceListEntity> getPriceList() {
        Query query = em.createQuery("SELECT p FROM PriceList p WHERE p.archived != 'true' ORDER BY p.tieredQuantity asc");
        ArrayList priceList = new ArrayList<PriceListEntity>();
        
        for (Object o : query.getResultList()) {
            PriceListEntity p = (PriceListEntity) o;
            priceList.add(p);
        }
        return priceList;
    }
   
}
